/**
 * 产品路由 - 基于后端共享数据库
 * 用户端可以浏览产品，管理端可以管理产品
 */

import express from 'express';
import { query } from '../config/database.js';

const router = express.Router();

// 获取产品列表
router.get('/', async (req, res) => {
    try {
        const { 
            page = 1, 
            limit = 12, 
            category, 
            search, 
            minPrice, 
            maxPrice,
            sortBy = 'created_at',
            sortOrder = 'DESC'
        } = req.query;

        const offset = (page - 1) * limit;
        
        // 构建查询条件
        let whereConditions = ['status = $1'];
        let queryParams = ['active'];
        let paramIndex = 2;

        if (category) {
            whereConditions.push(`category = $${paramIndex}`);
            queryParams.push(category);
            paramIndex++;
        }

        if (search) {
            whereConditions.push(`(name ILIKE $${paramIndex} OR description ILIKE $${paramIndex})`);
            queryParams.push(`%${search}%`);
            paramIndex++;
        }

        if (minPrice) {
            whereConditions.push(`price >= $${paramIndex}`);
            queryParams.push(parseFloat(minPrice));
            paramIndex++;
        }

        if (maxPrice) {
            whereConditions.push(`price <= $${paramIndex}`);
            queryParams.push(parseFloat(maxPrice));
            paramIndex++;
        }

        const whereClause = whereConditions.length > 0 ? 
            `WHERE ${whereConditions.join(' AND ')}` : '';

        // 获取产品总数
        const countResult = await query(
            `SELECT COUNT(*) FROM products ${whereClause}`,
            queryParams
        );
        const totalCount = parseInt(countResult.rows[0].count);

        // 获取产品列表
        const validSortColumns = ['name', 'price', 'created_at', 'sales_count'];
        const validSortOrders = ['ASC', 'DESC'];
        
        const sortColumn = validSortColumns.includes(sortBy) ? sortBy : 'created_at';
        const sortDirection = validSortOrders.includes(sortOrder.toUpperCase()) ? sortOrder.toUpperCase() : 'DESC';

        const productsResult = await query(
            `SELECT id, name, description, price, category, image_url, stock_quantity, 
                    sales_count, created_at, updated_at
             FROM products 
             ${whereClause}
             ORDER BY ${sortColumn} ${sortDirection}
             LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`,
            [...queryParams, limit, offset]
        );

        const products = productsResult.rows.map(product => ({
            id: product.id,
            name: product.name,
            description: product.description,
            price: parseFloat(product.price),
            category: product.category,
            imageUrl: product.image_url,
            stockQuantity: product.stock_quantity,
            salesCount: product.sales_count,
            createdAt: product.created_at,
            updatedAt: product.updated_at
        }));

        res.json({
            success: true,
            data: {
                products,
                pagination: {
                    page: parseInt(page),
                    limit: parseInt(limit),
                    totalCount,
                    totalPages: Math.ceil(totalCount / limit)
                }
            }
        });

    } catch (error) {
        console.error('获取产品列表错误:', error);
        res.status(500).json({
            success: false,
            message: '获取产品列表失败'
        });
    }
});

// 获取产品详情
router.get('/:id', async (req, res) => {
    try {
        const { id } = req.params;

        const productResult = await query(
            `SELECT id, name, description, price, category, image_url, stock_quantity, 
                    sales_count, created_at, updated_at
             FROM products 
             WHERE id = $1 AND status = $2`,
            [id, 'active']
        );

        if (productResult.rows.length === 0) {
            return res.status(404).json({
                success: false,
                message: '产品不存在'
            });
        }

        const product = productResult.rows[0];

        // 增加产品浏览量
        await query(
            'UPDATE products SET sales_count = sales_count + 1 WHERE id = $1',
            [id]
        );

        res.json({
            success: true,
            data: {
                product: {
                    id: product.id,
                    name: product.name,
                    description: product.description,
                    price: parseFloat(product.price),
                    category: product.category,
                    imageUrl: product.image_url,
                    stockQuantity: product.stock_quantity,
                    salesCount: product.sales_count + 1, // 包含当前访问
                    createdAt: product.created_at,
                    updatedAt: product.updated_at
                }
            }
        });

    } catch (error) {
        console.error('获取产品详情错误:', error);
        res.status(500).json({
            success: false,
            message: '获取产品详情失败'
        });
    }
});

// 获取产品分类
router.get('/categories/list', async (req, res) => {
    try {
        const categoriesResult = await query(
            'SELECT DISTINCT category FROM products WHERE status = $1 ORDER BY category',
            ['active']
        );

        const categories = categoriesResult.rows.map(row => row.category).filter(Boolean);

        res.json({
            success: true,
            data: {
                categories
            }
        });

    } catch (error) {
        console.error('获取产品分类错误:', error);
        res.status(500).json({
            success: false,
            message: '获取产品分类失败'
        });
    }
});

// 获取热门产品
router.get('/featured/popular', async (req, res) => {
    try {
        const { limit = 8 } = req.query;

        const productsResult = await query(
            `SELECT id, name, description, price, category, image_url, stock_quantity, 
                    sales_count, created_at
             FROM products 
             WHERE status = $1
             ORDER BY sales_count DESC, created_at DESC
             LIMIT $2`,
            ['active', limit]
        );

        const products = productsResult.rows.map(product => ({
            id: product.id,
            name: product.name,
            description: product.description,
            price: parseFloat(product.price),
            category: product.category,
            imageUrl: product.image_url,
            stockQuantity: product.stock_quantity,
            salesCount: product.sales_count,
            createdAt: product.created_at
        }));

        res.json({
            success: true,
            data: {
                products
            }
        });

    } catch (error) {
        console.error('获取热门产品错误:', error);
        res.status(500).json({
            success: false,
            message: '获取热门产品失败'
        });
    }
});

// 获取新品推荐
router.get('/featured/new', async (req, res) => {
    try {
        const { limit = 8 } = req.query;

        const productsResult = await query(
            `SELECT id, name, description, price, category, image_url, stock_quantity, 
                    sales_count, created_at
             FROM products 
             WHERE status = $1
             ORDER BY created_at DESC
             LIMIT $2`,
            ['active', limit]
        );

        const products = productsResult.rows.map(product => ({
            id: product.id,
            name: product.name,
            description: product.description,
            price: parseFloat(product.price),
            category: product.category,
            imageUrl: product.image_url,
            stockQuantity: product.stock_quantity,
            salesCount: product.sales_count,
            createdAt: product.created_at
        }));

        res.json({
            success: true,
            data: {
                products
            }
        });

    } catch (error) {
        console.error('获取新品推荐错误:', error);
        res.status(500).json({
            success: false,
            message: '获取新品推荐失败'
        });
    }
});

export default router;